package com.kuangjia.student_ems.repository;

import com.kuangjia.student_ems.entity.Leave;
import com.kuangjia.student_ems.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface LeaveRepository extends JpaRepository<Leave, Long> {
    
    List<Leave> findAllByOrderByCreateTimeDesc();
    
    List<Leave> findByUserOrderByCreateTimeDesc(User user);
    
    List<Leave> findByUserAndStatusOrderByCreateTimeDesc(User user, Leave.LeaveStatus status);
    
    List<Leave> findByStatusOrderByCreateTimeDesc(Leave.LeaveStatus status);
    
    long countByStatus(Leave.LeaveStatus status);
    
    @Query("SELECT COUNT(l) FROM Leave l WHERE l.type = :type")
    long countByType(Leave.LeaveType type);
    
    List<Leave> findByUserAndStartTimeBetweenOrderByCreateTimeDesc(User user, LocalDateTime startDate, LocalDateTime endDate);
    
    List<Leave> findByApproverOrderByCreateTimeDesc(User approver);
    
    List<Leave> findByUserDepartmentIdOrderByCreateTimeDesc(Long departmentId);
    
    @Query("SELECT l FROM Leave l WHERE l.user.department.id = :departmentId ORDER BY l.createTime DESC")
    List<Leave> findByDepartment(Long departmentId);
    
    @Query("SELECT l FROM Leave l WHERE l.user.department.id = :departmentId AND l.status = 'PENDING' ORDER BY l.createTime DESC")
    List<Leave> findPendingLeavesByDepartmentId(Long departmentId);
    
    @Query("SELECT l FROM Leave l WHERE l.user.department.id = :departmentId AND l.status = :status ORDER BY l.createTime DESC")
    List<Leave> findByDepartmentAndStatus(Long departmentId, Leave.LeaveStatus status);
    
    @Query("SELECT l FROM Leave l WHERE (l.startTime BETWEEN :startTime AND :endTime) OR (l.endTime BETWEEN :startTime AND :endTime) OR (l.startTime <= :startTime AND l.endTime >= :endTime) ORDER BY l.createTime DESC")
    List<Leave> findLeavesDuringPeriod(LocalDateTime startTime, LocalDateTime endTime);
    
    List<Leave> findByStartTimeGreaterThanEqualAndEndTimeLessThanEqualOrderByCreateTimeDesc(LocalDateTime startTime, LocalDateTime endTime);
    
    @Query("SELECT l FROM Leave l WHERE l.user.id = :userId AND ((l.startTime BETWEEN :startTime AND :endTime) OR (l.endTime BETWEEN :startTime AND :endTime) OR (l.startTime <= :startTime AND l.endTime >= :endTime)) ORDER BY l.createTime DESC")
    List<Leave> findUserLeavesDuringPeriod(Long userId, LocalDateTime startTime, LocalDateTime endTime);
    
    @Query("SELECT l FROM Leave l WHERE l.user.department.id = :departmentId AND ((l.startTime BETWEEN :startTime AND :endTime) OR (l.endTime BETWEEN :startTime AND :endTime) OR (l.startTime <= :startTime AND l.endTime >= :endTime))")
    List<Leave> findByDepartmentIdAndDateRange(Long departmentId, LocalDateTime startTime, LocalDateTime endTime);

    List<Leave> findByUserId(Long userId);
    
    List<Leave> findByUserIdOrderByCreateTimeDesc(Long userId);
    
    @Query("SELECT l FROM Leave l JOIN FETCH l.user u WHERE " +
           "LOWER(u.name) LIKE LOWER(:keyword) OR " +
           "LOWER(u.username) LIKE LOWER(:keyword) OR " +
           "LOWER(CAST(l.type AS string)) LIKE LOWER(:keyword) OR " +
           "LOWER(CAST(l.status AS string)) LIKE LOWER(:keyword) OR " +
           "LOWER(l.reason) LIKE LOWER(:keyword) OR " +
           "(l.approver IS NOT NULL AND LOWER(l.approver.name) LIKE LOWER(:keyword)) " +
           "ORDER BY l.createTime DESC")
    List<Leave> searchLeaves(@Param("keyword") String keyword);
} 